package test05;

public class t1_sql {
    public static void main(String[] args) {

//        1.查询在 SC 表存在成绩的学生信息
//        select st.Sname,st.Sage,st.Ssex from sc as sc left join student as st on st.Sid = sc.Sid where sc.score>0 group by st.Sid
//
//        2.查询「李」姓老师的数量
//        select count(*)as total from teacher where Tname like "李%"
//
//        3.查询学过「张三」老师授课的同学的信息  多表联合查询
//        select * from sc as sc left join student as st on st.Sid = sc.Sid left join course as co on co.Cid = sc.Cid where co.Cid = 02
//
//        4.查询男生、女生人数
//        select COUNT(*) as 总人数,Ssex AS 性别 from student GROUP BY Ssex
//
//        5.查询 1990 年出生的学生名单
//        select * from student where Sage like "1990%"
//
//        6.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
//        select co.Cname,avg(sc.score)as source from sc as sc left join course as co on co.Cid = sc.Cid group by Cname order by source desc,co.Cid asc
//
//        7.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
//        select st.Sid,st.Sname from sc as sc left join student as st on st.Sid = sc.Sid left join course as co on co.Cid = sc.Cid where co.Cid = 01 and sc.score>=80
//
//        8.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
//        select s.* , sc1.score , sc2.score from student s inner join (select * from sc where Cid = "01") sc1 on s.Sid = sc1.Sid inner join (select * from sc where Cid = "02") sc2 on s.Sid = sc2.Sid where sc1.score > sc2.score
    }
}
